/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package utils;

/**
 *
 * @author Alice
 */

import java.awt.Label;
import java.io.File;
  import java.io.IOException;
import java.io.OutputStream;
   
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
   
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletContext;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import servlets.ExcelServlet;


   
public class Excel{
    int riga=4;
    Sheet sheet;

       
    
    public void Genera(HttpServletRequest request,HttpServletResponse response,String excelFileName) throws FileNotFoundException, IOException, BiffException {
    
           HttpSession session=request.getSession();
           ServletContext ctx=request.getServletContext();
           String contextPath=ctx.getRealPath(File.separator);
        
        /**File file= new File(contextPath+"EXCEL/"+excelFileName);
           if(!file.exists()){
           file.mkdir();}*/
        
           try{
           
           //contextPath+"EXCEL/"+"/vendita.xls
      HSSFWorkbook wb = new HSSFWorkbook();
      HSSFSheet sheet = wb.createSheet("Sheet");
      
      HSSFRow row0 = sheet.createRow((short)0);
      row0.createCell((short) 0).setCellValue("Gestioni Vendita - Commissioni dovute");
      HSSFRow row3 = sheet.createRow((short)3);
      row3.createCell((short) 0).setCellValue("ID_UTENTE");
      row3.createCell((short) 1).setCellValue("USERNAME");
      row3.createCell((short) 2).setCellValue("ID_PRODOTTO");
      row3.createCell((short) 3).setCellValue("NOME_PRODOTTO");
      row3.createCell((short) 4).setCellValue("DATA/ORA");
      row3.createCell((short) 5).setCellValue("PREZZO (€)");
      row3.createCell((short) 6).setCellValue("PREZZO_SPEDIZIONE (€)");
      row3.createCell((short) 7).setCellValue("TOTALE(PREZZO+TASSA) (€)");
      
        List<Vendita> vend =(ArrayList<Vendita>)session.getAttribute("vendita");        
        for(int i=0; i<vend.size(); i++){
            int idProdotto=vend.get(i).getIdProdotto();
            int idU=vend.get(i).getIdUtente();
            String nomeProd=vend.get(i).getNomeProd();
            Timestamp dataOra=vend.get(i).getDataOra();
            float prez=vend.get(i).getPrezzo();
            float prezSped=vend.get(i).getPrezzo_Spedizione();
            float tot=vend.get(i).getTassaCasaDAsta();
            String username=vend.get(i).getUsername();
      
      HSSFRow rowX = sheet.createRow((short)riga);
      rowX.createCell((short) 0).setCellValue(idU);
      rowX.createCell((short) 1).setCellValue(username);
      rowX.createCell((short) 2).setCellValue(idProdotto);
      rowX.createCell((short) 3).setCellValue(nomeProd);
      //HSSFCell cell = rowX.createCell((short)4);
      //cell.setCellValue(new Date());
      //HSSFCellStyle cellStyle = wb.createCellStyle();
      //cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy/mm/dd hh:mm:ss"));
      //cell = rowX.createCell((short)4);
      //cell.setCellValue(dataOra);
      //cell.setCellStyle(cellStyle);
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
String time = sdf.format(dataOra.getTime());   
      rowX.createCell((short) 4).setCellValue(time);    
      rowX.createCell((short) 5).setCellValue(String.format("%.2f", prez));
      rowX.createCell((short) 6).setCellValue(String.format("%.2f", prezSped));
      rowX.createCell((short) 7).setCellValue(String.format("%.2f", tot));
        riga++;
        }
        FileOutputStream fileOut = new FileOutputStream(contextPath+"EXCEL/"+excelFileName);
      wb.write(fileOut);
      fileOut.close();
           
    } catch (FileNotFoundException ex) {
            Logger.getLogger(ExcelServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }}
      
      
      
     /** HSSFRow row = sheet.createRow((short)0);
      HSSFCell cell = row.createCell((short)0);
      cell.setCellValue(new Date());
      HSSFCellStyle cellStyle = wb.createCellStyle();
      cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy/MM/dd hh:mm:ss"));
      cell = row.createCell((short)1);
      cell.setCellValue(new Date());
      cell.setCellStyle(cellStyle);
    
      
      
      
      
      
      
      
      
        //Workbook workbook= Workbook.getWorkbook(new File(contextPath+"EXCEL/"+excelFileName));
        //sheet= workbook.getSheet(0);
        
        /**Cell a1= sheet.getCell(0, 0);
        Cell b2= sheet.getCell(1, 1);
        Cell c2= sheet.getCell(2, 1);
        
        String stringa1="String stringa1";
        stringa1= a1.getContents();
        String stringb2="String stringb2";
        stringb2= b2.getContents();
        String stringc2="String stringc2";
        stringc2= c2.getContents();
        
        String stringa11=null;
        double number22=0;
        Date data33=null;
        String title="Gestioni Vendita - Commissioni dovute";
        Cell a1= sheet.getCell(0, 0);
        String campo0="ID_UTENTE";String campo1="USERNAME";
        String campo2="ID_PRODOTTO";String campo3="NOME_PRODOTTO";
        String campo4="DATA/ORA";String campo5="PREZZO";
        String campo6="PREZZO_SPEDIZIONE";String campo7="TOTALE(PREZZO+TASSA)";
        Cell c0= sheet.getCell(3, 0);
        Cell c1= sheet.getCell(3, 1);
        Cell c2= sheet.getCell(3, 2);
        Cell c3= sheet.getCell(3, 3);
        Cell c4= sheet.getCell(3, 4);
        Cell c5= sheet.getCell(3, 5);
        Cell c6= sheet.getCell(3, 6);
        Cell c7= sheet.getCell(3, 7);
       
        
        if(a1.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)a1;
            title=lc.getString();    
        }
        if(c0.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c0;
            campo0=lc.getString();    
        }
        if(c1.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c1;
            campo1=lc.getString();    
        }
        if(c2.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c2;
            campo2=lc.getString();    
        }
        if(c3.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c3;
            campo3=lc.getString();    
        }
        if(c4.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c4;
            campo4=lc.getString();    
        }
        if(c5.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c5;
            campo5=lc.getString();    
        }
        if(c6.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c6;
            campo6=lc.getString();    
        }
        if(c7.getType()==CellType.LABEL){
            LabelCell lc= (LabelCell)c7;
            campo7=lc.getString();    
        }
        
        
        GeneraRiga(idProdotto,idU,nomeProd,dataOra,prez,prezSped,tot,username);
        }
    
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");

        workbook.write(fileOut);

fileOut.close();
        workbook.close();   
        
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ExcelServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
       }
    
    public void GeneraRiga(int idProdotto,int idU,String nomeProd,Timestamp dataOra,float prez,float prezSped,float tot,String username){
        
        Cell X0= sheet.getCell(riga, 0);
        Cell X1= sheet.getCell(riga, 1);
        Cell X2= sheet.getCell(riga, 2);
        Cell X3= sheet.getCell(riga, 3);
        Cell X4= sheet.getCell(riga, 4);
        Cell X5= sheet.getCell(riga, 5);
        Cell X6= sheet.getCell(riga, 6);
        Cell X7= sheet.getCell(riga, 7);
        if(X0.getType()==CellType.NUMBER){
            NumberCell nc= (NumberCell) X0;
            idU=(int) nc.getValue();
        }
        if(X1.getType()==CellType.LABEL){
            LabelCell nc= (LabelCell) X1;
            username= nc.getString();
        }
        if(X2.getType()==CellType.NUMBER){
            NumberCell nc= (NumberCell) X2;
            idProdotto=(int) nc.getValue();
        }        
        if(X3.getType()==CellType.LABEL){
            LabelCell nc= (LabelCell) X3;
            nomeProd= nc.getString();
        }
        if(X4.getType()==CellType.DATE){
            DateCell nc= (DateCell) X4;
            dataOra=(Timestamp) nc.getDate();
        }
        if(X5.getType()==CellType.NUMBER){
            NumberCell nc= (NumberCell) X5;
            prez=(float) nc.getValue();
        }
        if(X6.getType()==CellType.NUMBER){
            NumberCell nc= (NumberCell) X6;
            prezSped=(float) nc.getValue();
        }
        if(X7.getType()==CellType.NUMBER){
            NumberCell nc= (NumberCell) X7;
            tot=(float) nc.getValue();
        }
        riga++;
    }

}*/
    

